//201902104011 王雪梦
package bysj.dao;
import bysj.domain.Department;
import bysj.domain.Major;
import bysj.service.DepartmentService;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

public final class MajorDao {
    private static MajorDao majorDao = null;
    //将构造器私有，防止本类外的类生成本类的对象
    private MajorDao() {
    }
    //返回本类唯一的对象
    public static MajorDao getInstance() {
        // 静态变量majorDao为空时，才创建MajorDao对象。
        // 第二次调用本方法majorDao就不空了，不再创建对象。
        if(majorDao==null){
            majorDao = new MajorDao();
        }
        return majorDao;
    }

   /* public static void main(String[] args) throws SQLException {
        //从数据库中读取id=3的学院记录，转换为内存对象
        Department mgtDept = DepartmentDao.getInstance().find(3);
        System.out.println(mgtDept);
        //在内存中创建misMajor和pmMajor对象
        Major misMajor = new Major("房地产管理","020501","增加");
        Major pmMajor = new Major("土地资源管理","020601","增加");
        pmMajor.setDepartment(mgtDept);
        misMajor.setDepartment(mgtDept);
        //在内存中令两个对象和mgtDept建立关联关系
        MajorDao.getInstance().add(pmMajor);
        MajorDao.getInstance().add(misMajor);
        misMajor = MajorDao.getInstance().find(3);
        System.out.println(misMajor);
        //删除id=2的记录
        MajorDao.getInstance().delete(2);
        //查询表中所有的记录
        Collection<Major>majors = MajorDao.getInstance().findAll();
        System.out.println(majors);
    }*/

    public Collection<Major> findAll() throws SQLException {
        //创建集合类对象，用来保存所有记录代表的Major对象
        Collection<Major> majors = new TreeSet<>();
        //获得连接对象
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String major_sql = "select * from major";
        //创建预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement(major_sql);
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            //获得result的参数值
            int id = resultSet.getInt(1);
            String description = resultSet.getString(2);
            String no = resultSet.getString(3);
            String remarks = resultSet.getString(4);
            int schoolId = resultSet.getInt(5);
            Department department = DepartmentService.getInstance().find(schoolId);
            //为major赋值
            Major major = new Major(id,description,no,remarks,department);
            //将major添加到majors集合中
            majors.add(major);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet,preparedStatement,connection);
        return majors;
    }

    public Major find(Integer id) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        Major major = null;
        //创建sql语句
        String major_sql = "SELECT * FROM major WHERE id = ?";
        //创建预编译对象
        PreparedStatement pstmt= connection.prepareStatement(major_sql);
        //为预编译对象赋值
        pstmt.setInt(1,id);
        //获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        while(resultSet.next()){
            //获得result的参数值
            String description = resultSet.getString(2);
            String no= resultSet.getString(3);
            String remarks= resultSet.getString(4);
            int schoolId= resultSet.getInt(1);
            Department department = DepartmentDao.getInstance().find(5,connection);
            //为major对象赋值
            major = new Major(schoolId,description,no,remarks,department);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, pstmt, connection);
        return major;
    }

    public boolean update(Major major) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        //准备预编译语句
        String major_sql = "update major set description=?,no=?,remarks=? ,department_id=? where id="+major.getId();
        //创建预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement(major_sql);
        //为预编译对象赋值
        preparedStatement.setString(1,major.getDescription());
        preparedStatement.setString(2,major.getNo());
        preparedStatement.setString(3,major.getRemarks());
        preparedStatement.setInt(4,major.getDepartment().getId());
        //执行预编译对象的executeUpdate方法，获得修改的记录行数
        int rowAdd = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement,connection);
        return rowAdd>0;
    }

    public boolean add(Major major) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        //创建预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement("Insert into major(description,no,remarks,department_id) Values "+"(?,?,?,?)");
        //为预编译对象赋值
        preparedStatement.setString(1,major.getDescription());
        preparedStatement.setString(2,major.getNo());
        preparedStatement.setString(3,major.getRemarks());
        preparedStatement.setInt(4,major.getDepartment().getId());
        //执行预编译对象的executeUpdate方法，获得增加的记录行数
        int rowAdd = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement,connection);
        return rowAdd>0;
    }

    public boolean delete(Integer id) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        //创建sql语句
        String major_sql = "delete from major where id=?";
        //在该连接创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(major_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //执行预编译对象的executeUpdate方法，获得删除的记录行数
        int affectedRowNum = pstmt.executeUpdate();
        JdbcHelper.close(pstmt,connection);
        return affectedRowNum>0;
    }
}
